<?php

/**
 * he 上报dbf操作类
 *
 * @version 1.0
 * @date 2013-12-13
 */
class DefaultController extends FController {

	public $user_school_list;
	public $user_subject_list;
	public $user_role_list;
	public $exam_id;

	public function accessRules() {
		$config = array('index' => 1);
		return ZRole::auth("report_rule", $config);
	}
 
	public function __common() {
		parent::init();
		if (empty($this->cLoginUser)) {
			$this->redirect($this->createUrl('/site/login'));
		} else {
			$userLoginUser = $this->cLoginUser;
			$this->user_role_list = isset($userLoginUser['cuser']['role_id']) ? $userLoginUser['cuser']['role_id'] : '';
			$this->user_school_list = isset($userLoginUser['cuser']['school']['school_id']) ? $userLoginUser['cuser']['school']['school_id'] : '';
			$this->user_subject_list = isset($userLoginUser['cuser']['school']['subject_id']) ? $userLoginUser['cuser']['school']['subject_id'] : '';
			$zRow = new ZRole();
			//得到学校对应的考试
			$exam_list = $zRow->_getSchoolExam($this->user_school_list);
			if (!empty($exam_list)) {
				$this->exam_id = implode(',', $exam_list);
			}
		}
		$this->commonScript();
		Yii::app()->clientScript->registerCssFile(THEME_CSS_URL . '/module_Sip.css');
	}

	//列表
	public function actionIndex() {
		$this->__common();
		$this->title = "上报DBF参数设置";
		$reportrule = new Reportrule();
		$limit = 8;
		$info = Yii::app()->request->getParam('info');
		if (!empty($info['id'])) {
			$reportrule = $reportrule->findByPk($info['id']);
			$info['add_time'] = time();
			$info['create_user'] = $this->cLoginUser['cuser']['id'];
			$info['create_time'] = time();
			$info['update_user'] = $this->cLoginUser['cuser']['id'];
			$info['update_time'] = time();
			$reportrule->attributes = ZTask::dis_input($info);
			$reportrule->save();
		}
		$ruleArray = $date['reportrule'] = array();
		$ruleData = Reportrule::model()->find(' "id" > 0');
		if (!empty($ruleData)) {
			$ruleArray = $ruleData->attributes;
			
			$date['reportrule'] = $ruleArray;

			$sql = 'SELECT * FROM "{{reportrule_detail}}" WHERE "report_id" = ' . $date['reportrule']['id'] . ' ORDER BY TO_NUMBER("number") ASC';
			$count = 'SELECT COUNT("id") FROM "{{reportrule_detail}}" WHERE "report_id" = ' . $date['reportrule']['id'];
			$data = ZCommon::getDaoPageList($sql, $count, $limit);
		}

		$pageUrl = $this->createUrl('/report_rule/default/index');
		$this->render('index', array(
		'data' => isset($data['listDatas']) ? $data['listDatas'] : '',
		'pages' => isset($data['pages']) ? $data['pages'] : '',
		'serch' => $date,
		'pageUrl' => $pageUrl
		));
	}

	//更新
	public function actionUpdate() {
		$this->layout = "//layouts/main_window";
		$data['id'] = Yii::app()->request->getParam('id');

		$reportruleDetail = new ReportruleDetail();
		$infos = $reportruleDetail->findByPk($data['id']);
		if ($infos) {
			$infos = $infos->attributes;
		}
		if (!empty($_POST['submit'])) {
			$info = Yii::app()->request->getParam('info');
			$reportruleDetail = $reportruleDetail->findByPk($info['id']);
			$info['status'] = (!empty($info['status'])) ? $info['status'] : 0;
			$checkcount = $reportruleDetail->count(' "id" != ' . $info['id'] . ' AND ("number"=\'' . $info['number'] . '\' OR "name"=\'' . $info['name'] . '\')');
			if ($checkcount > 0) {
				$this->frameAjaxMessage(null, "序号和名称不可重复！");	exit;

			}
			$info['add_time'] = time();
			$info['create_user'] = $this->cLoginUser['cuser']['id'];
			$info['create_time'] = time();
			$info['update_user'] = $this->cLoginUser['cuser']['id'];
			$info['update_time'] = time();
			$reportruleDetail->attributes = $info;
			if ($reportruleDetail->save()) {
				$this->frameAjaxMessage('/report_rule/default/index');
				exit;
			} else {
				$this->frameAjaxMessage(null, "操作失败!");
				exit;
			}
		} else {
			$this->render('update', array('data' => $infos));
			exit;
		}
	}

	//添加
	public function actionAdd() {
		$this->layout = "//layouts/main_window";
		$date['id'] = Yii::app()->request->getParam('id');
		if (!empty($_POST['submit'])) {
			$reportruleDetail = new ReportruleDetail();
			$data = Yii::app()->request->getParam('info');
			$checkcount = $reportruleDetail->count('"number"=\'' . $data['number'] . '\' OR "name"=\'' . $data['name'] . '\'');
			if ($checkcount > 0) {
				$this->frameAjaxMessage(null, "序号和名称不可重复！");exit;

			}
			$data['add_time'] = time();
			$data['create_user'] = $this->cLoginUser['cuser']['id'];
			$data['create_time'] = time();
			$data['update_user'] = $this->cLoginUser['cuser']['id'];
			$data['update_time'] = time();
			$reportruleDetail->attributes = $data;
			if ($reportruleDetail->save()) {
				$this->frameAjaxMessage('/report_rule/default/index');
			} else {
				$this->frameAjaxMessage(null, "数据添加失败！");exit;

			}
		} else {
			$this->render('add', array('date' => $date));
		}
	}

	//删除
	public function actionDelete() {
		$id = (int) Yii::app()->request->getparam('id');
		if (!empty($id)) {
			$model = ReportruleDetail::model()->findByPk($id);
			if (empty($model)) {
				$this->showmessage("您要删除的数据不存在!");
				exit;
			}
			if ($model->model()->deleteAll(' "id"=:id', array(':id' => $id))) {
				$this->redirect($this->createUrl('/report_rule/default/index'));
			} else {
				$this->showmessage("删除失败!");
			}
		} else {
			$this->showmessage("删除失败,参数错误!");
		}
	}

	/*
	* 上报 DBF  导出
	*/

	public function actionOutlist() {
		$this->__common();
		$conn = Yii::app()->db;
		$this->title = "上报DBF导出";
		$limit = 8;
		$where = '';
		
		$date = array();
		$qWhere = '';
		//权限判断
		if ($this->user_role_list != 1 && !empty($this->exam_id)) {
			$where = ' AND "exam_id" IN (' . $this->exam_id . ')';
			$zExam = new zExam();
        	if(!empty($this->user_school_list)){
            	$qWhere = ' AND S."school_id" IN (' . $this->user_school_list . ')';
        	}
		}
	
		$date['ztask'] = ZTask::_getExamAll($where);
		$date['school'] = ExamData::_getSchoolList($this->user_school_list);
		$date['subject'] = ZProblemNum::_getAllsubject();
		$date['class'] = ZTask::_getclass();
		$date['disciplin'] = ExamData::_getDisciplin();
		
		
		$info['exam_id'] = Yii::app()->request->getParam('exam_id');
		$info['school_id'] = Yii::app()->request->getParam('school_id');
		$info['subject_id'] = Yii::app()->request->getParam('subject_id');
		$info['disciplin_id'] = Yii::app()->request->getParam('disciplin_id');
		$info['class_name'] = Yii::app()->request->getParam('class_name');
		$info['nojoinstatus'] = Yii::app()->request->getParam('nojoinstatus');
		$info['import'] = Yii::app()->request->getParam('import');
		$info['disciplin_code'] = Yii::app()->request->getParam('disciplin_code');
		
		$where = '';
		$order = ' ORDER BY S."student_id" ASC ';

	
		if (!empty($info['exam_id'])) {
			$where.= ' AND S."exam_id" = ' . $info['exam_id'];
		}
		if (!empty($info['school_id'])) {
			$where.= ' AND S."school_id" = ' . $info['school_id'];
		}else{
			$where .= $qWhere;
		}
		
		if (!empty($info['disciplin_id'])) {
			$where.= ' AND S."disciplin_type" =\'' . $info['disciplin_id'].'\'';
		}
		if (!empty($info['class_name'])) {
			$where.= ' AND S."class_name" = \'' . $info['class_name'].'\'';
		}
		if (!empty($info['nojoinstatus'])) {
			//$where.= ' AND S."statis" = \'' . $info['nojoinstatus'].'\'';
			if($info['nojoinstatus'] == 'Y'){
				$where.= ' AND s."exam_code" IS NOT NULL';
			}elseif($info['nojoinstatus'] == 'N'){
				$where.= ' AND s."exam_code" IS NULL';
			}else{
				$where.= ' AND s."statis" = \'' . $info['nojoinstatus'].'\'';
			}
		}
		
		#  科别的    #
		if(!empty($info['disciplin_code'])){
			$disciplin_data = ExamData::_getDisciplin($info['disciplin_code']);
			$where.= ' AND s."disciplin_name" = \''.$disciplin_data['disciplin_nick'].'\'';
		}
		
		
		if (!empty($info['subject_id'])) {
			$where.= ' AND P."subject_id" = ' . $info['subject_id'];
			$sql = 'SELECT * FROM "{{student}}" S LEFT JOIN "{{point}}" P ON S."student_id"=P."student_id" AND S."exam_id" = P."exam_id" WHERE 1=1' . $where . $order;
			$count = 'SELECT COUNT(S."student_id") FROM "{{student}}" S LEFT JOIN "{{point}}" P ON S."student_id" = P."student_id" AND S."exam_id" = P."exam_id" WHERE 1=1 ' . $where;
		}else{
			$sql = 'SELECT * FROM "{{student}}" S WHERE 1=1' . $where . $order;
			$count = 'SELECT COUNT(S."student_id") FROM "{{student}}" S WHERE 1=1 ' . $where;
		}	
		 
		if (isset($info['import'])) {
			if ($info['import'] == '导出') {
				$this->import($sql);
				exit;
			}
		}
	
		$data = ZCommon::getDaoPageList($sql, $count, $limit);

		$pageUrl = $this->createUrl('/report_rule/default/outlist' ,$info);
		//DBF规则
		$reportrulesql = 'SELECT * FROM "{{reportrule}}" ORDER BY "id" asc';
		$ruledata =  Yii::app()->db->createcommand($reportrulesql)->queryRow();
		if (!empty($ruledata)){
			$detailsql = 'SELECT * FROM "{{reportrule_detail}}" WHERE "report_id" = '.$ruledata	['id'].' AND "status" = 1 ORDER BY TO_NUMBER("number") ASC';
			$detaildata =  Yii::app()->db->createcommand($detailsql)->queryAll();
			$info['detaildata'] = $detaildata;
		}

		$examInfo = array();
		if(!empty($date['ztask'])){
			foreach($date['ztask'] as $eValue){
				$examInfo[$eValue['exam_id']] = $eValue;
			}
		}
		
		$this->render('outlist', array(
			'data' => $data['listDatas'],
			'pages' => $data['pages'],
			'pageUrl' => $pageUrl,
			'date' => $date,
			'info' => $info,
			'examInfo' => $examInfo
		));
	}

	//导出
	public function import($sql) {
		$this->__common();
		
		$zExamArray  = new zExam();
		
		//DBF规则
		$reportrulesql = 'SELECT * FROM "{{reportrule}}" ORDER BY "id" asc';
		$ruledata = Yii::app()->db->createcommand($reportrulesql)->queryRow();
		if (!empty($ruledata)) {
			$detailsql = 'SELECT * FROM "{{reportrule_detail}}" WHERE "report_id" = ' . $ruledata['id'] . ' AND "status" = 1 ORDER BY TO_NUMBER("number") ASC';
			$detaildata = Yii::app()->db->createcommand($detailsql)->queryAll();
		}

		$zimu = array(0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F', 6 => 'G', 7 => 'H', 8 => 'I', 9 => 'J', 10 => 'K', 11 => 'L', 12 => 'M', 13 => 'N', 14 => 'O', 15 => 'P', 16 => 'Q', 17 => 'R', 18 => 'S', 19 => 'T', 20 => 'U', 21 => 'V', 22 => 'W',23=>'X',24=>'W',25=>'Y',26=>'Z');
		spl_autoload_unregister(array('YiiBase', 'autoload'));
		include_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/extensions/PHPExcel.php');
		include_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/extensions/PHPExcel/IOFactory.php');
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0);
		$objActSheet = $objPHPExcel->getActiveSheet();
		
		foreach ($detaildata as $k => $v) { //标题栏显示
			$objActSheet->setCellValue($zimu[$k] . '1', $v['name']);
			$objActSheet->getColumnDimension($zimu[$k])->setWidth(16);
		}
		
		$data = Yii::app()->db->createcommand($sql)->queryAll();
		 
		$objPHPExcel->setActiveSheetIndex(0);
		$objActSheet = $objPHPExcel->getActiveSheet();
		$i = 2;
		
		$examArray = '';
		$eArray = array();

		foreach ($data as $k => $v) {
			if(isset($eArray[$v['exam_id']])){
				if(!empty($eArray[$v['exam_id']])){
					$examArray = $eArray[$v['exam_id']]['exam_term_name'];
				}
			}else{
				$eArray[$v['exam_id']] = $zExamArray->_getExamInfo($v['exam_id']);
				if(!empty($eArray[$v['exam_id']])){
					$examArray = $eArray[$v['exam_id']]['exam_term_name'];
				}
			}
			$rowvalue = '';
			foreach ($detaildata as $p => $d) {	
				if (!empty($d['value'])) {
					$rowvalue = $d['value'];
				} else {
					if($d['row']=='exam_id'){
						$rowvalue = $examArray;
					}else{
						$rowvalue = isset($v[$d['row']])?$v[$d['row']]:'';
					}
				}
				
				$objActSheet->setCellValue($zimu[$p] . $i, $rowvalue);
			}
			$i++;
		};
		$objPHPExcel->setActiveSheetIndex(0);
		
		$file_nams = $ruledata['name'];
		$file_nams .= ".dbf";
		if (preg_match("/MSIE/", $_SERVER["HTTP_USER_AGENT"])) {
			$file_nams = urlencode($file_nams);
			$file_nams = str_replace("+", "%20", $file_nams); // 替换空格
		}
		ob_end_clean();
		header("Content-Type: application/vnd.ms-excel;charset=utf-8");
		header("Content-Disposition: attachment; filename=" . $file_nams);
		header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
		header("Content-Type:application/download");
		header('Expires:0');
		header('Pragma:public');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		Yii::app()->end();
		spl_autoload_register(array('YiiBase', 'autoload'));
		exit();
	}
//导出
	public function imports($sql) {
		
		$dbf_url ="test.dbf";//DBF生成路径
		//DBF规则
		$reportrulesql = 'SELECT * FROM "{{reportrule}}" ORDER BY "id" asc';
		$ruledata = Yii::app()->db->createcommand($reportrulesql)->queryRow();
		if (!empty($ruledata)) {
			$detailsql = 'SELECT * FROM "{{reportrule_detail}}" WHERE "report_id" = ' . $ruledata['id'] . ' ORDER BY  "number" ASC ';
			$detaildata = Yii::app()->db->createcommand($detailsql)->queryAll();
		}

		//$zimu = array(0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F', 6 => 'G', 7 => 'H', 8 => 'I', 9 => 'J', 10 => 'K', 11 => 'L', 12 => 'M', 13 => 'N', 14 => 'O', 15 => 'P', 16 => 'Q', 17 => 'R', 18 => 'S', 19 => 'T', 20 => 'U', 21 => 'V', 22 => 'W',23=>'X',24=>'W',25=>'Y',26=>'Z');
		spl_autoload_unregister(array('YiiBase', 'autoload'));
		//include_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/extensions/PHPExcel.php');
		//include_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/extensions/PHPExcel/IOFactory.php');
		//$objPHPExcel = new PHPExcel();
		//$objPHPExcel->setActiveSheetIndex(0);
		//$objActSheet = $objPHPExcel->getActiveSheet();
		
		$def = array();
        //  设置dbf字段
		foreach ($detaildata as $k => $v) {
			switch ($v['type']){
				case 'varchar':
					$def[] = array($v['name'],'C',$v['length']);
					break;
				case 'int':
					$def[] = array($v['name'],'N',$v['length'],0);
					break;
				case 'float':
					$def[] = array($v['name'],'N',$v['length'],$v['float']);
					break;
				case 'bit':
					$def[] = array($v['name'],'L');
					break;
				default:
					$def[] = array($v['name'],'C',255);
					break;
			}
			
			//$objActSheet->setCellValue($zimu[$k] . '1', $v['name']);
			//$objActSheet->getColumnDimension($zimu[$k])->setWidth(16);
		}
		
		// 创建dbf文档
		if (!dbase_create($dbf_url, $def)) {
        	echo "Error,   can 't   create   the   database\n ";
       	}
		$data = Yii::app()->db->createcommand($sql)->queryAll();
		//print_r($data);
		// 打开dbf文档
		$dbz = dbase_open('test.dbf', 2);
		//$objPHPExcel->setActiveSheetIndex(0);
		//$objActSheet = $objPHPExcel->getActiveSheet();
		
		// 写入数据
		foreach ($data as $k => $v) {
			foreach ($detaildata as $p => $d) {
				$rowvalue = '';
				if ($d['value'] == 'gdz') {
					$rowvalue = $d['value'];
				} else {
					$rowvalue = $v[$d['row']];
				}
				if($d['row']=='roll'){
					$rowvalue = ' '.$rowvalue;
				}
				$tempArray[] = $rowvalue;
				
				//$objActSheet->setCellValue($zimu[$p] . $i, $rowvalue);
			}
			
			dbase_add_record($dbz,array('殷德彬','123456'));
			$tempArray = array();
		};
		//$objPHPExcel->setActiveSheetIndex(0);
		//$title = $ruledata['name'];
		//$file_nams = $title;
		//$file_nams .= ".xls";
		//if (preg_match("/MSIE/", $_SERVER["HTTP_USER_AGENT"])) {
		//	$file_nams = urlencode($file_nams);
		//	$file_nams = str_replace("+", "%20", $file_nams); // 替换空格
		//}
		ob_end_clean();
		//header("Content-Type: application/vnd.ms-excel;charset=utf-8");
		//header("Content-Disposition: attachment; filename=" . $file_nams);
		//header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
		//header("Content-Type:application/download");
		//header('Expires:0');
		//header('Pragma:public');
		//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		//$objWriter->save('php://output');
		//Yii::app()->end();
		spl_autoload_register(array('YiiBase', 'autoload'));
		dbase_close($dbz);
		exit();
	}
	/**
     * @ 批量删除学段
     * @ yindebin
     * @ 2014-01-06 pm
     *
     */
	public function actionDelete_all() {
		$id = Yii::app()->request->getparam('id');
		if (!empty($id)) {
			$delete = 'DELETE FROM "{{reportrule}}" WHERE "id" IN (' . $id . ')';
			$delete1 = 'DELETE FROM  "{{reportrule_detail}}" WHERE "id" IN (' . $id . ')';
			$query = Yii::app()->db->createCommand($delete)->execute();
			$query = Yii::app()->db->createCommand($delete1)->execute();
			echo 1;
		} else {
			echo 2;
		}
	}

}